#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import dianputools
import json
import time
import re

def get_all_product_of_day(dtstart):
    dbh = dianputools.connect_to_shangpin()
    #dbh = dianputools.connect_to_50()
    #q = 'select ProductID from t_product where date(AddDate) = "%s";' % (dtstart)
    q = 'select ProductID from t_product where ProductID>%s order by ProductID asc'% (dtstart)
    #q = 'select prod_id from t_product order by prod_id asc ;'
    products = dianputools.query(dbh, q)

    return [p[0] for p in products]

def get_second_cat(cate_id,dbh ):
    #dbh = dianputools.connect_to_50()
    query = 'select fullpath from t_dispcategory where cateid =%s and depth=1;' % (cate_id)
    #print query
    fullpath = dianputools.get_a_string(dbh, query)
    #print fullpath
    if fullpath == None:
        q = "select fullpath from t_category where cateid=%s" % cate_id
        fullpath = dianputools.get_a_string(dbh, q)
        #print cate_id,fullpath
        if fullpath != None:
            q="select cateid,catename from t_category where cateid in (%s) order by depth asc" % fullpath
            rt = dianputools.query(dbh, q)
            return rt
        else:
            return None
    #print cate_id    
    q = "select dispcategoryid, catename from t_dispcategory where dispcategoryid in (%s) order by depth asc" % fullpath
    rt = dianputools.query(dbh, q)
    
    return rt

def get_thirth_cat(cate_id ,param,dbh):
    
    #print param
    #dbh = dianputools.connect_to_50()
    query = 'select param_id from t_cate_param where cate_id = %s;' % cate_id
    param_id = dianputools.get_a_string(dbh, query)
    #print param_id
    if param_id == None:
        return get_second_cat(cate_id ,dbh)
    
    reg = '{"I":%s,"V":"(\w*)"}'%param_id
    #print reg
    pattern =  re.compile(reg)
    matchs = pattern.search(param)
    #print reg
    objval = "0"
    #print matchs
    try:
        objval = matchs.group(1)
    except Exception,e:
        return get_second_cat(cate_id,dbh )
        
    filter_str = "objecttype=%s"%objval
    #print filter_str
    query = "select fullpath from t_dispcategory where cateid =%s and filter='%s'" % (cate_id,filter_str)
    fullpath = dianputools.get_a_string(dbh, query)
    #print fullpath
    if fullpath == None:
        return get_second_cat(cate_id ,dbh)
    
    q = "select dispcategoryid, catename from t_dispcategory where dispcategoryid in (%s) order by depth asc" % fullpath
    rt = dianputools.query(dbh, q)
    #print q
    return rt

def get_citys(city,dbh):
    if city == 0 :
        return None
    
    #dbh = dianputools.connect_to_50()
    q = 'select fullpath from t_local where localid=%s' % city
    fullpath = dianputools.get_a_string(dbh, q)
    
    if fullpath is None:
        return None
    
    q = "select localid, localname from t_local where localid in (%s) order by depth asc" % fullpath
    #print q
    rt = dianputools.query(dbh, q)
    
    return rt

def get_shopname_from_shopid(s):
    if s=='0':
        return ''
    
    dbh = dianputools.connect_to_dianpu()
    q = 'select shopName from shop where id=%s' % s
    shopname = dianputools.get_a_string(dbh, q)
    return shopname

def get_shop_info_by_shopid(s,dbh):
    if s=='0':
        return '',0
    
    q = 'select shopName,freeOrPay from shop where id=%s' % s
    items = dianputools.query(dbh, q);
    if len(items) > 0:
        shop = dianputools.query(dbh, q)[0]   
        
        if shop != None:
            if len(shop) > 0:
                if shop[0] != None:
                    try:
                        return shop[0][0],shop[0][1]
                    except Exception:
                        pass
        
    return '',0

def get_product_property(p,dbh,dbh_prod,dbh_shop):
    #print p
    desc = ['pid']
    stats = [p]

    q = "select replace(replace(Title,'\\'',''),'\\\\',' '),OnTime,ifnull(OffTime,'0000-00-00'),maxPrice,distributionType,salestate,shopid,AddDate,State,cateid,param,local,salestate,userid,minprice from t_product where ProductID=%s;" % (p)
    #print q
    pinfo = list(dianputools.query(dbh_prod, q)[0])
    if pinfo[8] != '1':
        pinfo[8] = '0'

    desc += ['title', 'onlinetime', 'offlinetime', 'perprice', 'isdelivery', 'pstate', 'sellerid', 'publictime', 'isverify','cate_id','sale_state','user_id','min_price']
    stats += [pinfo[0],pinfo[1],pinfo[2],pinfo[3],pinfo[4],pinfo[5],pinfo[6],pinfo[7],pinfo[8],pinfo[9],pinfo[12],pinfo[13],pinfo[14]]

    shopname,shoptype = get_shop_info_by_shopid(pinfo[6],dbh_shop)
    if shopname != None:
        shopname = shopname.replace("'","\"")
        
    #print shopname
    desc += ['sellername','shop_type']
    stats += [shopname,shoptype]

    dc = get_thirth_cat(pinfo[9],pinfo[10],dbh)
    
    cat1 = 0
    cat1name = ''
    cat2 = 0
    cat2name=''
    cat3 = 0
    cat3name=''
    if dc != None:
        if len(dc) > 0:
            cat1 = dc[0][0]
            cat1name = dc[0][1]
        if len(dc) > 1:
            cat2 = dc[1][0]
            cat2name=dc[1][1]
        if len(dc) > 2:
            cat3 = dc[2][0]
            cat3name=dc[2][1]
    #print cat2name    
    desc += ['cat1','cat1name', 'cat2','cat2name','cat3','cat3name']
    stats += [cat1,cat1name, cat2,cat2name,cat3,cat3name]
    #print stats
    
    local = pinfo[11]
    city_id = 0
    city_name = ''
    area_id = 0
    area_name = ''
    local_id= 0
    local_name = ''
    
    if local != None and local !='':
        city = json.loads(local)[0]['L']        
        dc = get_citys(city,dbh)
        #print city
        if dc != None:
            if len(dc) == 1:
                city_id = dc[0][0]
                city_name = dc[0][1]
            elif len(dc)==2:
                city_id = dc[0][0]
                city_name = dc[0][1]
                area_id = dc[1][0]
                area_name = dc[1][1]
            elif len(dc)>2:
                city_id = dc[0][0]
                city_name = dc[0][1]
                area_id = dc[1][0]
                area_name = dc[1][1]
                local_id= dc[2][0]
                local_name = dc[2][1]
            
    #print city_name
    desc += ['city_id','cityname','area_id','areaname', 'local_id','localname']
    stats += [city_id,city_name, area_id,area_name,local_id,local_name]
    
    desc +=['param']
    stats += [pinfo[10]]
    #print desc, stats
    return desc, stats

def get_yesterday(sep='-'):
    fmt=sep.join(['%Y','%m','%d'])
    return time.strftime(fmt,time.localtime(time.time()-24*60*60))
    
if __name__ == '__main__':
    
    dtstart = get_yesterday()
    print dtstart
    
    cityids = []
    desc = []
    
    line_list = []
    
    dbh = dianputools.connect_to_50()
    dbh_prod = dianputools.connect_to_shangpin()
    dbh_shop = dianputools.connect_to_dianpu()
    #q = 'select max(pid) from product_property'
    #pid = dianputools.get_a_string(dbh, q)
    pid =0
    tablename = 'product_info'
    #print pid
    products = get_all_product_of_day(pid)
    # print products
    for p in products:
        desc, pstats = get_product_property(p,dbh,dbh_prod,dbh_shop)
        # print desc, pstats
        #dianputools.insertinto(dbh, tablename, desc, pstats)
        #line_list.append('\t'.join(map(lambda i : str(i), stats)))
        #print '\t'.join(desc)
        line_list.append( '\t'.join(map(lambda i : str(i), pstats)))

    
    cont = '\n'.join(line_list)
    path = "/work/opt/analytic/lh_tmp_data/shop/"
    file_name = "product_info_%s.txt"%dtstart
    #print path
    dianputools.writeTextToFile(path,file_name,"w",cont)
    if cont != '\n':
        try:
            q = 'truncate table product_info;'
            tmp = dianputools.get_a_string(dbh, q)
        except Exception,e:
            print e
        dianputools.load_data(dbh,path+file_name,tablename,'\t')
        